package com.team.hiring.company.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.team.hiring.common.entity.PageModel;
import com.team.hiring.common.util.DBUtil;
import com.team.hiring.company.entity.PositionEntity;

public class PositionDao {
	/**
	 * 查询所有职位信息
	 * 
	 * @param id
	 * @return
	 */
	public List<PositionEntity> positionList(int companyid) {
		List<PositionEntity> positionList = new ArrayList<PositionEntity>();
		
		Connection connection = DBUtil.getConnection();
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT p.id,p.company_id,p.name,p.duty,p.ability,p.salary1,p.salary2,p.release_time FROM position p WHERE p.company_id=?";
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, companyid);
			resultSet = prepareStatement.executeQuery();
			while (resultSet.next()) {
				PositionEntity positionEntity = new PositionEntity();
				positionEntity.setId(resultSet.getInt("p.id"));
				positionEntity.setName(resultSet.getString("p.name"));
				positionEntity.setDuty(resultSet.getString("p.duty"));
				positionEntity.setAbility(resultSet.getString("p.ability"));
				positionEntity.setSalary1(resultSet.getInt("p.salary1"));
				positionEntity.setSalary2(resultSet.getInt("p.salary2"));
				positionEntity.setReleaseTime(resultSet
						.getString("p.release_time"));
				positionList.add(positionEntity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, prepareStatement, connection);
		}
		return positionList;
	}

	public boolean addposition(PositionEntity positonEntity) {
		boolean result = false;
		Connection connection = DBUtil.getConnection();
		PreparedStatement prepareStatement = null;
		try {
			String sql = "INSERT INTO position VALUES(NULL,?, ?, ?,?, ?,?,now());";
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, positonEntity.getCompanyId());
			prepareStatement.setString(2, positonEntity.getName());
			prepareStatement.setString(3, positonEntity.getDuty());
			prepareStatement.setString(4, positonEntity.getAbility());
			prepareStatement.setInt(5, positonEntity.getSalary1());
			prepareStatement.setInt(6, positonEntity.getSalary2());
			int row = prepareStatement.executeUpdate();
			if (row != 0) {
				result = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(prepareStatement, connection);
		}
		return result;
	}

	/**
	 * 修改前的查询，查询完跳转到修改界面
	 * 
	 * @param id
	 * @return
	 */
	public List<PositionEntity> goUpdateUI(int id) {
		List<PositionEntity> positionList = new ArrayList<PositionEntity>();
		PositionEntity positionEntity = new PositionEntity();
		Connection connection = DBUtil.getConnection();
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT p.id,p.company_id,p.name,p.duty,p.ability,p.salary1,p.salary2,p.release_time FROM position p WHERE p.id=?";
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, id);
			resultSet = prepareStatement.executeQuery();
			while (resultSet.next()) {
				positionEntity.setId(resultSet.getInt("p.id"));
				positionEntity.setCompanyId(resultSet.getInt("p.company_id"));
				positionEntity.setName(resultSet.getString("p.name"));
				positionEntity.setDuty(resultSet.getString("p.duty"));
				positionEntity.setAbility(resultSet.getString("p.ability"));
				positionEntity.setSalary1(resultSet.getInt("p.salary1"));
				positionEntity.setSalary2(resultSet.getInt("p.salary2"));
				positionEntity.setReleaseTime(resultSet
						.getString("p.release_time"));
				positionList.add(positionEntity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, prepareStatement, connection);
		}
		return positionList;
	}

	/**
	 * 正式修改
	 * 
	 * @param positionEntity
	 * @return
	 */
	public int updatepositionsubmit(PositionEntity positionEntity) {
		int result = 0;
		Connection connection = DBUtil.getConnection();
		PreparedStatement prepareStatement = null;
		try {
			String sql = "update position p set p.name=?,p.duty=?,p.ability=?,p.salary1=?,p.salary2=? WHERE p.id=?;";
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setString(1, positionEntity.getName());
			prepareStatement.setString(2, positionEntity.getDuty());
			prepareStatement.setString(3, positionEntity.getAbility());
			prepareStatement.setInt(4, positionEntity.getSalary1());
			prepareStatement.setInt(5, positionEntity.getSalary2());
			prepareStatement.setInt(6, positionEntity.getId());
			int row = prepareStatement.executeUpdate();
			if (row != 0) {
				result = row;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(prepareStatement, connection);
		}
		return result;
	}

	
	/**
	 * 删除
	 * 
	 * @param id
	 * @return
	 */
	public int delete(int id) {
		return DBUtil.executeUpdate("DELETE FROM position WHERE id=?", id);
	}

	
	
	/**
	 * 进入主页时显示所有职位
	 * @param pageSize 
	 * @param pageNo 
	 * @return
	 */
	public PageModel<PositionEntity> selectAllPosition(int pageNo, int pageSize) {
		List<PositionEntity> positionList = new ArrayList<PositionEntity>();
		
		Connection connection = DBUtil.getConnection();
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT p.id,p.name,p.duty,p.ability,p.salary1,p.salary2,c.id as 'company$id',c.name,"
					+ "c.address,c.slogan,p.release_time FROM position p,company c "
					+ "WHERE p.company_id=c.id order by p.id  LIMIT ?,?";
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, (pageNo - 1) * pageSize);
			prepareStatement.setInt(2, pageSize);
			resultSet = prepareStatement.executeQuery();
			while (resultSet.next()) {
				PositionEntity positionEntity = new PositionEntity();
				positionEntity.setId(resultSet.getInt("p.id"));
				positionEntity.setName(resultSet.getString("p.name"));
				positionEntity.setDuty(resultSet.getString("p.duty"));
				positionEntity.setAbility(resultSet.getString("p.ability"));
				positionEntity.setSalary1(resultSet.getInt("p.salary1"));
				positionEntity.setSalary2(resultSet.getInt("p.salary2"));
				positionEntity.setCompany$id(resultSet.getInt("company$id"));
				positionEntity.setCompanyName(resultSet.getString("c.name"));
				positionEntity.setCompanyAddress(resultSet.getString("c.address"));
				positionEntity.setCompanySlogan(resultSet.getString("c.slogan"));
				positionEntity.setReleaseTime(resultSet
						.getString("p.release_time"));
				positionList.add(positionEntity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, prepareStatement, connection);
		}
		return new PageModel<PositionEntity>(positionList, getTotalRecords(),
				pageNo, pageSize);
	}

	
	
	
	/**

	 * 查询总共有多少条职位

	 * 

	 * @return

	 */
	private int getTotalRecords() {
		int totalRecords = 0;
		String sql = "SELECT COUNT(*) AS 'totalRecords' FROM position";
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			totalRecords = resultSet.getInt("totalRecords");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, preparedStatement, connection);
		}
		return totalRecords;
	}

	
	
	
	
	/**
	 * 主页点击职位名称后跳转到详细职位信息界面
	 * @param positionId
	 * @return
	 */
	public PositionEntity homepagePositionDateilInfo(int positionId) {
		PositionEntity positionEntity = new PositionEntity();
		Connection connection = DBUtil.getConnection();
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT c.name,p.id,p.company_id,p.name,p.duty,p.ability,p.salary1,p.salary2,p.release_time FROM position p,company c "
					   + "WHERE c.id=p.company_id AND p.id=?";
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, positionId);
			resultSet = prepareStatement.executeQuery();
			while (resultSet.next()) {
				positionEntity.setCompanyName(resultSet.getString("c.name"));
				positionEntity.setId(resultSet.getInt("p.id"));
				positionEntity.setName(resultSet.getString("p.name"));
				positionEntity.setDuty(resultSet.getString("p.duty"));
				positionEntity.setAbility(resultSet.getString("p.ability"));
				positionEntity.setSalary1(resultSet.getInt("p.salary1"));
				positionEntity.setSalary2(resultSet.getInt("p.salary2"));
				positionEntity.setReleaseTime(resultSet
						.getString("p.release_time"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, prepareStatement, connection);
		}
		return positionEntity;
	}
	
	
	/**
	 * Ajax追加信息
	 * @param request
	 * @param response
	 * @throws IOException 
	 */
	public List<PositionEntity> AjaxAddPositon(int pageNo, int pageSize) {
		List<PositionEntity> positionList = new ArrayList<PositionEntity>();
		Connection connection = DBUtil.getConnection();
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT p.id,p.name,p.duty,p.ability,p.salary1,p.salary2,c.id as 'company$id',c.name,"
					+ "c.address,c.slogan,p.release_time FROM position p,company c "
					+ "WHERE p.company_id=c.id order by p.id  LIMIT ?,?";
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, (pageNo - 1) * pageSize);
			prepareStatement.setInt(2, pageSize);
			resultSet = prepareStatement.executeQuery();
			while (resultSet.next()) {
				PositionEntity positionEntity = new PositionEntity();
				positionEntity.setId(resultSet.getInt("p.id"));
				positionEntity.setName(resultSet.getString("p.name"));
				positionEntity.setDuty(resultSet.getString("p.duty"));
				positionEntity.setAbility(resultSet.getString("p.ability"));
				positionEntity.setSalary1(resultSet.getInt("p.salary1"));
				positionEntity.setSalary2(resultSet.getInt("p.salary2"));
				positionEntity.setCompany$id(resultSet.getInt("company$id"));
				positionEntity.setCompanyName(resultSet.getString("c.name"));
				positionEntity.setCompanyAddress(resultSet.getString("c.address"));
				positionEntity.setCompanySlogan(resultSet.getString("c.slogan"));
				positionEntity.setReleaseTime(resultSet
						.getString("p.release_time"));
				positionList.add(positionEntity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, prepareStatement, connection);
		}
		return positionList;
	}
	
	
	
}
